Re: [GENERAL] datetime problems

At 17:14 +0200 on 21/10/98, Memphisto wrote:


> Another problem. I issued the following query:
> select * from annex_log where login_start
>     between
>     (select date_trunc('month','now'::datetime)) and
>     (select date_trunc('month','now'::datetime)) +
>     timespan('1 month'::reltime);
>
> but postgresql said there's a parse error near select
> while the following query did work
>
> select * from annex_log where login_start >=
>     (select date_trunc('month','now'::datetime)) and login_start <
>     (select date_trunc('month','now'::datetime) +
>     timespan('1 month'::reltime));
>
> Why?

Probably because the parentheses are not balanced in the first query. But
why the subqueries, anyway? Why not simply:

SELECT * FROM annex_log
WHERE login_start
    BETWEEN date_trunc('month','now'::datetime)
        AND ( date_trunc('month','now'::datetime) + '1 month'::timespan);

(Note that I also removed the redundant type conversion you did on '1 month').

Again, I recommend doing the >=, < thing rather than 'between', because
'between' will also allow the actual value of 1998-09-01 (for example) to
be included. It's a close interval, rather than a half-open one.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma